<?php

class ExportController extends AppController {

	var $name = 'Export';
	var $helpers = array('Html', 'Form', 'Csv');
	var $components = array('Functions');

	function beforeRender() {
		$groupid = $this->Auth->user('group_id');
		$name = 'Export';
		IF($groupid != 1){
			$result1 = mysql_query("SELECT id FROM applications WHERE name = '$name'");
			$appid = mysql_fetch_object($result1)->id;
			$result2 = mysql_query("SELECT count(*) as count FROM groupshasrights WHERE group_id = '$groupid' AND application_id = '$appid'");
			$count = mysql_fetch_object($result2)->count;
			IF($count == 0){
				$this->Session->setFlash(__('Access Denied', true));
				$this->redirect(array('controller' => 'pages' ,'action' => 'error'));	
			}
		}
	}

    function intrumexport() {
        $this->layout = null;
        $this->autoLayout = false;
        $runid = $this->params['url']['runid'];
        $this->loadModel('Customer');
        $macstatus = 24; // -> picked
		$result = $this->Customer->query("SELECT c.id, c.account_number, c.salutation, c.inst_last_name, c.inst_first_name, c.bill_street, c.bill_house_no, c.bill_zip, c.bill_city FROM macs AS m, runshasmacs AS rhm, customers AS c, customershasmacs AS chm WHERE m.id = rhm.mac_id AND c.id = chm.customer_id AND m.id = chm.mac_id AND m.status_id = '$macstatus' AND rhm.run_id = '$runid' AND NOT EXISTS( SELECT * FROM blacklists AS b WHERE m.id = b.mac_id )");
		$this->set(compact('result','runid')); 
    }
    
    
	function schmiedundfehrexport() {
        $this->layout = null;
        $this->autoLayout = false;
        $runid = $this->params['url']['runid'];
        $pid = $this->params['url']['pid'];
        $this->loadModel('Customer');
        $macstatus = 24; // -> picked
        $checkdtv = $this->Functions->checkdtv2($pid);
		$result = $this->Customer->query("SELECT c.account_number, c.inst_first_name, c.inst_last_name, c.salutation, c.company_name, c.po_box, c.language, c.bill_street, c.bill_house_no, c.bill_zip, c.bill_city, m.mac, mt.description, mst.name FROM macs AS m, mactypes AS mt, runshasmacs AS rhm, customers AS c, customershasmacs AS chm, macservicetypes AS mst WHERE m.id = rhm.mac_id AND m.mactype_id = mt.id AND c.id = chm.customer_id AND m.id = chm.mac_id AND m.status_id = '$macstatus' AND rhm.run_id = '$runid' AND m.macservicetype_id = mst.id AND NOT EXISTS( SELECT * FROM blacklists AS b WHERE m.id = b.mac_id)");
		$this->set(compact('result','runid','pid','checkdtv')); 
    }
    
    
	function ctspexport() {
        $this->layout = null;
        $this->autoLayout = false;
        $runid = $this->params['url']['runid'];
        $this->loadModel('Customer');
        $macstatus = 24; // -> picked
		$result = $this->Customer->query("SELECT m.mac FROM macs AS m, runshasmacs AS rhm, customers AS c, customershasmacs AS chm WHERE m.id = rhm.mac_id AND c.id = chm.customer_id AND m.id = chm.mac_id AND m.status_id = '$macstatus' AND rhm.run_id = '$runid' AND NOT EXISTS( SELECT * FROM blacklists AS b WHERE m.id = b.mac_id )");
		$this->set(compact('result','runid')); 
    }
    
    
	function interactionexport() {
        $this->layout = null;
        $this->autoLayout = false;
        $runid = $this->params['url']['runid'];
        $pid = $this->params['url']['pid'];
        $this->loadModel('Customer');
        $macstatus = 24; // -> picked
        $checkdtv = $this->Functions->checkdtv2($pid);
		$result = $this->Customer->query("SELECT c.account_number FROM macs AS m, runshasmacs AS rhm, customers AS c, customershasmacs AS chm WHERE m.id = rhm.mac_id AND c.id = chm.customer_id AND m.id = chm.mac_id AND m.status_id = '$macstatus' AND rhm.run_id = '$runid' AND NOT EXISTS( SELECT * FROM blacklists AS b WHERE m.id = b.mac_id )");
		$this->set(compact('result','runid','pid','checkdtv')); 
    }
    
    
    function macexport() {
    	$this->layout = null;
        $this->autoLayout = false;
        $runid = $this->params['url']['runid'];
        $this->loadModel('Mac');
        $macstatus = 24; // -> picked
		$result = $this->Mac->query("SELECT m.mac FROM runshasmacs AS rhm, macs AS m WHERE rhm.run_id = '$runid' AND rhm.mac_id = m.id AND m.status_id = '$macstatus' AND NOT EXISTS (SELECT * FROM blacklists AS b WHERE m.id = b.mac_id)");
		$this->set(compact('result','runid')); 	
    }
    
    
    function badpayers(){
    	$this->layout = null;
        $this->autoLayout = false;
        $pid = $this->params['url']['pid'];
        $this->loadModel('Blacklist');
        $problem = 2; // --> Bad Payers
		$result = $this->Blacklist->query("SELECT c.account_number, c.mac_address, c.serial_no, p.name FROM blacklists AS b, customershasmacs AS chm, customers AS c, problems AS p, runshasmacs rhm, runs run WHERE b.mac_id = chm.mac_id AND chm.customer_id = c.id AND b.problem_id = p.id AND chm.mac_id = rhm.mac_id AND rhm.run_id = run.id AND p.id = '$problem' AND run.project_id = '$pid'");
		$this->set(compact('result')); 
    }
    
    
    function partnernetworks(){
    	$this->layout = null;
        $this->autoLayout = false;	
        $pid = $this->params['url']['pid'];
        $this->loadModel('Blacklist');
        $problem = 15; // --> Partnernet not Docsis 3.0 capable
		$result = $this->Blacklist->query("SELECT c.account_number, c.mac_address, c.serial_no, p.name FROM blacklists AS b, customershasmacs AS chm, customers AS c, problems AS p, runshasmacs rhm, runs run WHERE b.mac_id = chm.mac_id AND chm.customer_id = c.id AND b.problem_id = p.id AND chm.mac_id = rhm.mac_id AND rhm.run_id = run.id AND p.id = '$problem' AND run.project_id = '$pid'");
		$this->set(compact('result')); 
    }
    
    
	 function interactions(){
    	$this->layout = null;
        $this->autoLayout = false;	
        $pid = $this->params['url']['pid'];
        $this->loadModel('Blacklist');
        $problem = 15; // --> Partnernet not Docsis 3.0 capable
		$result = mysql_query("SELECT r.id AS BATCH_ID, r.batchstarttime AS BATCHPROCESS_DATE, c.mac_address AS MAC_ADDRESS, c.account_number AS ACCOUNT_NUMBER, ci.interact_id AS INTERACTION_ID, create_date AS CREATED_DATE, ci.inserted_by AS INSERTED_BY, ci.type AS TYPE, ci.product AS PRODUCT, ci.reason AS REASON, ci.result AS RESULT, ci.title As TITLE  FROM runs r, runshasmacs rhm, customershasmacs chm, customers c, customerinteractions ci WHERE r.id = rhm.run_id AND rhm.mac_id = chm.mac_id AND chm.customer_id = c.id AND c.account_number = ci.x_accno AND r.project_id = '$pid'");
		$this->set(compact('result')); 
    }
    
}
?>
